package co.yixiang.hw.repository;

import co.yixiang.hw.domain.YxStoreCouponUser;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
* @author Walert
* @date 2020-03-10
*/
public interface YxStoreCouponUserRepository extends JpaRepository<YxStoreCouponUser, Integer>, JpaSpecificationExecutor<YxStoreCouponUser> {


    @Query(value = "select " +
            "   t.* " +
            "from yx_store_coupon_user t " +
            "left join yx_store_coupon m1 on (t.cid = m1.id) " +
            "left join yx_store_coupon_product m2 on (t.cid = m2.cid) " +
            "where t.uid = ?1 and ((m1.type in (2,3) and m2.product_id = ?2) or (m1.type = 4)) and t.status = 0 and t.is_fail = 0 and use_time = 0 and end_time >= ?3",
            nativeQuery = true)
    List<YxStoreCouponUser> getUserCouponList(Integer uid, Integer goodsId,Integer time);


    @Query(value = " select " +
            "   t.id                    as id " +
            "   ,t.cid                  as cid " +
            "   ,t.uid                  as uid " +
            "   ,t.issue_coupon_id      as issueCouponId " +
            "   ,t.coupon_title         as couponTitle " +
            "   ,t.coupon_price         as couponPrice " +
            "   ,t.use_min_price        as useMinPrice " +
            "   ,m1.product_ids         as productIds " +
            "   ,m1.type                as couponType " +
            "from " +
            "   yx_store_coupon_user t " +
            "   left join yx_store_coupon m1 on ( t.cid = m1.id ) " +
            "   left join yx_store_coupon_product m2 on ( t.cid = m2.cid ) " +
            "where " +
            "   t.uid = ?1  and t.`status` = 0  and t.is_fail = 0  and use_time = 0 " +
            "   and end_time >= ?2  and m1.is_del = 0 " +
            "   group by t.id ",
            nativeQuery = true)
    List<Map> getUserCouponListV2(Integer uid, Integer time);

    List<YxStoreCouponUser> getByUidAndIssueCouponIdAndStatus(Integer uid,Integer issueCouponId, Integer status);

}